package persistencia;
import model.*;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;



public class LineBillMapper {

	private static LineBillMapper s_lineBillMapper;

	private LineBillMapper() {
	}

	public static LineBillMapper GetInstancia() {
		if (s_lineBillMapper == null) {
			s_lineBillMapper = new LineBillMapper();
		}

		return s_lineBillMapper;
	}
	
	public void InsertLineBill(LineBill lb, String linea, ArrayList<LineBillItem> billDetails)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
	
			String senten = "INSERT INTO LineBill (lineNumber, startDate, endDate) VALUES (?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, linea);
			ps.setString(2, lb.getActualOpenDate());
			ps.setString(3, lb.getNextCloseDate());
	

			int id  = ps.executeUpdate();
			for (LineBillItem lineBillItem : billDetails) {
				String sentenciaLB = "INSERT INTO LineBillItem (lineBillId, serviceName, costo, datetime, unidades) VALUES (?,?,?,?,?,?)";
				
				PreparedStatement pslb = null;
				pslb = con.prepareStatement(senten);
				pslb.setInt(1, Integer.parseInt(linea));
				pslb.setString(2, lineBillItem.getService().getServiceName());
				pslb.setFloat(3, lineBillItem.getCost());
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
				java.sql.Timestamp timestamp;
				try {
					timestamp = (Timestamp) dateFormat.parse(lineBillItem.getDate());					
					pslb.setTimestamp(4, timestamp);
					pslb.setInt(6, lineBillItem.getUnitComsuption());	
					pslb.execute();
				} catch (ParseException e) {
					System.out.println("Mensaje Error al Insertar Usuario: "
							+ e.getMessage());
					System.out.println("Stack Trace al Insertar Usuario: "
							+ e.getStackTrace());
				}
				
			}
		

		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar bill item: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar bill item: "
					+ e.getStackTrace());
					}
		finally
		{
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		
	}
	
	public void UpdateLineBill(LineBillItem lb, int lineBillId)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		String statement = "INSERT INTO LineBillItem (lineBillId, ServiceName, costo, datetime, unidades) VALUES (?,?,?,?,?)";
		
		try
		{
			PreparedStatement pslb = null;
			pslb = con.prepareStatement(statement);
			pslb.setInt(1,lineBillId);
			pslb.setString(2, lb.getService().getServiceName());
			pslb.setFloat(3, lb.getCost());
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
			java.sql.Timestamp timestamp;
	
			try {
				timestamp =  new Timestamp(dateFormat.parse(lb.getDate()).getTime());					
				pslb.setTimestamp(4, timestamp);
				pslb.setInt(5, lb.getUnitComsuption());	
				pslb.execute();
			} catch (ParseException e) {
				System.out.println("Mensaje Error al UPDATE LINE BILL: "
						+ e.getMessage());
				System.out.println("Stack Trace al UPDATE LINE BILL: "
						+ e.getStackTrace());
			}			
		}
		catch(SQLException ex)
		{
			
		}
		finally
		{
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	
	public void DeleteLineBill(int lineBillId)
	{
		// Obtener conexion BD
		Connection con = PoolConnection.getPoolConnection().getConnection();

		// Generar sentencia SQL
		try {
			// Preparar sentencia insert en tabla destino
			String senten = "DELETE from LineBill where LineBillId = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1, lineBillId);
			ps.execute();

			PoolConnection.getPoolConnection().realeaseConnection(con);

		} catch (SQLException e) {
			System.out.println("Mensaje Error al Borrar el LineBill: "
					+ e.getMessage());
			System.out.println("Stack Trace al Borrar el LineBill: "
					+ e.getStackTrace());
		}
		finally
		{
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		
	}
	
	public LineBill BuscarLineBill(String lineNumber)
	{
		LineBill lb = null;
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			String senten = "SELECT lineNumber, startDate, endDate from LineBill where lineNumber = ?" ;
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1,Integer.parseInt(lineNumber));
			ResultSet result = ps.executeQuery();
			//get bill 
			while (result.next())
			{
				lb = new LineBill(result.getInt("lineNumber"),
							result.getString("startDate"),
							result.getString("endDate"));
			}
			//get Items
			LineBillItem item = new LineBillItem();
			ArrayList<LineBillItem> items =  new ArrayList<LineBillItem>();
			senten = "SELECT * from LineBillitem where LineBillId = ?" ;
			ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1,Integer.parseInt(lineNumber));
			result = ps.executeQuery();
			while (result.next())
			{
				item = new LineBillItem(
						ServiceMapper.GetInstancia().BuscarService(result.getString("ServiceName")),
				result.getFloat("Costo"),result.getInt("Unidades"),lb,result.getString("DateTime"));
				items.add(item);
			}
			lb.populateBillItems(items);
			PoolConnection.getPoolConnection().realeaseConnection(con);
		  }
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al buscar LineBill: " + e.getMessage());
				System.out.println("Stack Trace al bucar linebillr: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
		finally
		{
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
      return lb;
	}

	public void DeleteLineBill(String number) {
		LineBill lb = null;
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			String deleteItemStatement = "Delete from linebillitem where LineBillId =?" ;
			String deleteBillStatement = "Delete from linebill where lineNumber =?" ;
			PreparedStatement ps = null;
			//delete items
			ps = con.prepareStatement(deleteItemStatement);
			ps.setString(1,number);
			ps.executeUpdate();
			//delete Bill
			ps = con.prepareStatement(deleteBillStatement);
			ps.setString(1,number);
			ps.executeUpdate();
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Line Bill deletion: "
				+ e.getMessage());
			System.out.println("Stack Trace for Line Bill deletion error: "
				+ e.getStackTrace());
		PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
}
